/*
@Coding: utf-8
@Author: yizhigopher
@Time: 2022/10/24 19:40
@Software: IntelliJ IDEA
*/

import java.sql.*;

public class MySQLFuncTest {
    public static Connection getConnection() {
        Connection con = null; // 声明连接
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            // 声明驱动
            con = DriverManager.getConnection(
                    //我这里的数据库名是student  user是root  没有密码
                    "jdbc:mysql://localhost:3306/db5", "root", "root");
            System.out.println("连接成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con; // 返回连接
    }

    //增
    public static void addUser(Student stu) {
        Connection con = getConnection();
        Statement st = null;

        try {
            st = con.createStatement();
            String sql = "call addOne('" + stu.StudentId +"', '"
                    + stu.StudentName + "', '"
                    + stu.Sage + "', '"
                    + stu.Ssex + "', '"
                    + stu.DeptId + "', '"
                    + stu.SClass + "')";
            st.execute(sql);
            System.out.println("插入成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //删
    public static void deleteUser(String Student_id) {
        Connection con = getConnection();
        Statement st = null;

        try {
            st = con.createStatement();
            String sql = "call deleteOne(" + Student_id + ")";
            st.execute(sql);
            System.out.println("删除成功");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //改
    public static void updateUser(Student stu) {
        Connection con = getConnection();
        Statement st = null;

        try {
            st = con.createStatement();
            String sql = "call updateOne('"+ stu.StudentId +"', '"
                    + stu.StudentName + "', '"
                    + stu.Sage + "', '"
                    + stu.Ssex + "', '"
                    + stu.DeptId + "', '"
                    + stu.SClass + "')";
            st.execute(sql);
            System.out.println("修改成功");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //查
    public static void search(String id) {
        Connection con = MysqlTest.getConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();
            String sql = "call selectOne(" + id + ")";
            rs = st.executeQuery(sql);
            System.out.println("查询结果如下：\n");
            while (rs.next()) {

                System.out.println("id:" + rs.getString("Student_id") + " name:" + rs.getString("Sname")
                        + " age:" + rs.getInt("Sage") + " sex:" + rs.getString("Ssex")
                        + " Dept_id:" + rs.getString("Dept_id")
                        + " Sclass:" + rs.getString("Sclass") );
            }
            System.out.println("\n");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
